<html>
    <head>
        <title>- CSV Excel Import -</title>
    </head>
    <body>
        <form>
            <input type="file" name="F1" id="F1" size="80"><input type="button" value="Read" name="B1" id="B1" onclick="execFile()">
			<input type="reset" value="Reset" name="B2" id="B2" onclick="renderArea.innerHTML=''">
			<input type="button" value="Process" name="B3" id="B3" onclick="ProcessImport();">
			<input type="button" value="ResetAll" name="B4" id="B4" onclick="resetAll()">
        </form>
        <div id="renderArea">
        </div>
        <script type="text/javascript" src="gears_init.js">
        </script>
        <script>
            // -- CSV PARSER --
            // author&nbsp; : Purbayu, 30Sep2008
            // email&nbsp;&nbsp; : purbayubudi@gmail.com
            //
            // description :
            //&nbsp; This jscript code describes how to load csv file and parse it into fields.
            //&nbsp; Additionally, a function to display html table as result is added.
            //
            // disclamer:
            //&nbsp; To use this code freely, you must put author's name in it.
            var db;
			
            //LoadXPIE();
            init();
			var renderArea = document.getElementById("renderArea");
			
			function resetAll(){
				db.execute('drop table Import;');
				db.execute('create table if not exists Import (Factura int,Cliente text, Clientid int,BalanceRegistrado real,BalanceActual real,Fecha text,Producto text, Productid int,Precio real,Cantidad real,Notas text);');
				db.execute('delete from sale_2009');
				db.execute('delete from saleitem_2009');
				db.execute('delete from salepayment_2009');
				db.execute('delete from customerpayment_2009');
				//db.execute('delete from customer');
				//db.execute('delete from product');
				//db.execute('delete from producttype');
				
			}
            
            
            function execFile(){
                // main function to open, parse, and then render
                var myfile = document.getElementById("F1");
                
                // verify file extension (csv or not)
                if (myfile.value.match(/\.csv$/gi) == ".csv") {
                    // create progress window..
                    
                    /*
                     var progressWindow = window.open("","","top=10,left=10,height=100,width=200");
                     progressWindow.document.write("<html><head></head><body><div id='progressArea'></div></body></html>");
                     var progressArea = progressWindow.document.getElementById("progressArea");
                     */
                    // load csv file and split it line by line
                    var arr = readCSV(myfile.value);
                    
                    // parse csv line by line, start at 1 to skip header
                    for (var i = 1; i < arr.length; i++) {
						arr[i] = parseLineCSV(arr[i]);
						renderArea.innerHTML = "Status: " + i + "/" + arr.length;
                        
                        
                        //progressArea.innerHTML="Parsing: "+(i+1)+" of "+arr.length;
                    }
					alert('Done');
                    return;
                    // render the result into html table
                    s = '<table border=1>';
                    for (var i = 0; i < arr.length-1; i++) {
                        s = s + '<tr>';
                        for (var j = 0; j < arr[i].length; j++) {
                            s = s + '<td><font size="1" face="Verdana">' + arr[i][j] + '</font></td>';
                        }
                        //progressArea.innerHTML="Table rendering: "+(i+1)+" of "+arr.length;
                        s = s + '</tr>';
                    }
                    s = s + '</table>';
                    
                    // close progress window after all tasks are completed
                    // progressWindow.close();
                }
                else {
                    // show this if user tries to open non csv file
                    s = '<b><font size="1" face="Verdana" color="#FF0000">Not a CSV file!</font></b>';
                }
                
                renderArea.innerHTML = s;
            }
            
            //The following code is depicted from Professional Javascript for
            // Web Developers, a book by Nicholas C.Zakas
            /*
             if (typeof XMLHttpRequest == "undefined" && window.ActiveXObject) {
             function XMLHttpRequest() {
             var arrSignatures = ["MSXML2.XMLHTTP.5.0", "MSXML2.XMLHTTP.4.0",
             "MSXML2.XMLHTTP.3.0", "MSXML2.XMLHTTP",
             "Microsoft.XMLHTTP"];
             for (var i=0; i < arrSignatures.length; i++) {
             try {
             var oRequest = new ActiveXObject(arrSignatures[i]);
             return oRequest;
             } catch (oError) {
             //ignore
             }
             }
             throw new Error("MSXML is not installed on your system.");
             }
             }
             */
            function init(){
                var success = false;
                
                if (window.google && google.gears) {
                    try {
                        db = google.gears.factory.create('beta.database');
                        
                        if (db) {
                            db.open('psdb');
                        }
                        
                    } 
                    catch (ex) {
                    }
                }
            }
            
            function readCSV(locfile){
                // load a whole csv file, and then split it line by line
                
                if (window.XMLHttpRequest) {
                    xhttp = new XMLHttpRequest();
                }
                else {
                    xhttp = new ActiveXObject("Microsoft.XMLHTTP");
                }
                xhttp.open("GET", locfile, false);
                
                xhttp.send("");
                xmlDoc = xhttp.responseXML;
                
                /*
                 if (window.XMLHttpRequest)
                 req = new XMLHttpRequest();
                 alert('here');//req.responseXML);
                 req.open("GET",locfile,false);
                 req.send("");*/
                return xhttp.responseText.split(/\n/g);
            }
            
            function parseLineCSV(lineCSV){
                // parse csv line by line into array
                var CSV = new Array();
                
                // Insert space before character ",". This is to anticipate 'split' in IE
                // try this:
                //
                // var a=",,,a,,b,,c,,,,d";
                // a=a.split(/\,/g);
                // document.write(a.length);
                //
                // You will see unexpected result!
                //
                
                //lineCSV = lineCSV.replace(/,/g," ,");
                
				lineCSV = lineCSV.split(/;/g);
                //column ordinal, do something
				//Factura,Clientid,BalanceRegistrado,BalanceActual,Fecha,Productid,Precio,Cantidad,Notas
                var Factura = lineCSV[0];
				var Client = lineCSV[1];
			    var Clientid = lineCSV[2];
			    var BalanceRegistrado = lineCSV[3];
			    var BalanceActual = lineCSV[4];
				var Fecha = lineCSV[5];
				var Product = lineCSV[6];
				var Productid = lineCSV[7];
				var Precio = lineCSV[8];
				var Cantidad = lineCSV[9];
				var Notas = lineCSV[14];
				
				    //0      1         2           3             4           5       
				//Factura,Cliente,Clientid,BalanceRegistrado,BalanceActual,Fecha,
				//    6      7         8      9       10       11    12    13    14
				//Producto,productid,Precio,Cantidad,SubTotal,Tax 1,Tax 2,Total,Notas
                db.execute('Insert into Import (Factura,Cliente,Clientid,BalanceRegistrado,BalanceActual,Fecha,Producto,Productid,Precio,Cantidad,Notas) values (?,?,?,?,?,?,?,?,?,?,?)', 
				[Factura, Client, Clientid, BalanceRegistrado, BalanceActual, Fecha,Product, Productid,Precio,Cantidad,Notas]);
				    
                return CSV;
            }
			
			function ProcessImport()
			{
				 
				var dbSaleName = "Sale_2009";
				var dbSalePaymentName = "SalePayment_2009";
				var dbSaleItemName ="SaleItem_2009";
				var dbSaleName = "Sale_2009";
				var CustomerPaymentDBName = "CustomerPayment_2009";
				
				query = "delete from import where cliente='undefined'";
				db.execute(query);
				
				
				//Insert Sales
				query = 'Insert into ' + dbSaleName + ' (id, customerid, saledate, memo, status) ';
				query+= ' Select Factura,Clientid, Fecha,Notas,1 from Import where productid <> 1' ; //exclude payments on account abono
				db.execute(query);
				//alert('sales');
				
				//Insert SaleItem
				
				query = 'Insert into ' + dbSaleItemName + ' (id,saleid,itemid,description,qty,cost,price,taxes)';
				query+= " Select Factura,Factura,productid,producto, Cantidad,(select cost from Product where id=productid),Precio,0 from Import where productid<>1" ; //exclude payments
				var rs = db.execute(query); 
			//	alert('salesitem');
				
				//Insert Payments, cash sales
				 query = 'Insert into ' + dbSalePaymentName + ' (id,saleid,paytype,amount) ';
				 query+= "Select Factura,Factura,'Cash',Precio from Import where productid<>1 and clientid=1" ; //exclude payments
				 db.execute(query); 
			//	 alert('cash');
				
				 //Insert Payments, credit sales
				 query = 'Insert into ' + dbSalePaymentName + ' (id,saleid,paytype,amount) ';
				 query+= "Select Factura,Factura,'Account',Precio from Import where productid<>1 and clientid<>1" ; //exclude payments
				 db.execute(query); 
				//  alert('credit');
				
				//Customer Payments				
				 query = 'insert into ' + CustomerPaymentDBName + ' (id, customerid,paymentdate, paytype, amount)';
				 query+= "Select factura,clientid,Fecha,'Cash',Precio from Import where productid=1 and clientid<>1" ; //exclude payments
				 db.execute(query); 
				// alert('payment');
				 
				 //create payments from Abono from other imported sales (not from excel)
				 query = 'insert into customerpayment_2009 (id , customerid, paymentdate, paytype, amount)';
				 query += " select si.saleid,customerid,saledate,'Cash', amount from sale_2009 s inner join saleitem_2009 si on si.saleid=s.id inner join salepayment_2009 sp on sp.saleid=s.id where itemid=1";
				 db.execute(query); 
			//	alert('Abonos');
				
				
//delete those sales
				query = 'delete from sale_2009';
				query += " where id in (select saleid from saleitem_2009  where itemid=1)"
				db.execute(query); 
				query = 'delete from salePayment_2009';
				query += " where id in (select saleid from saleitem_2009  where itemid=1)"
				db.execute(query); 
  				query = 'delete from saleitem_2009';
				query += "  where itemid=1"
				db.execute(query); 
				//alert('abonos gone');

 	
						 
//client new				 
				query = 'insert into Customer (id, firstname, middlename, lastname, balance, creditlimit)';
				query += " select distinct clientid,Cliente,'','NEW', 0,100 from Import"
				query += ' where clientid not in (select id from customer)';
				db.execute(query); 
				//alert('clients');
 				
 					//creates new customers from excel  
				 query = ' drop table if exists balances';
				 db.execute(query); 
				 query = ' create table balances (factura int,clientid int,balance real)';
			     db.execute(query);
				//alert('balances');
				 
				 query = "insert into balances (factura,clientid,balance) select distinct max(factura), clientid, 0 from import group by clientid, cliente having clientid <> 1 and clientid<>'undefined'";
			    	db.execute(query);
				 
				//and updates balances
					
				query =  "update balances set balance=(select balanceactual from import i where i.clientid=balances.clientid and i.factura=balances.factura)";
				db.execute(query);
				
				
				//update inventory
				query = "create table inventory (qty real, productid int);"
				db.execute(query);
				query = "insert into inventory (qty, productid) select sum(cantidad), productid from import group by productid;"
				db.execute(query);
				query = "update product set quantity=quantity-(select qty from inventory i where id=i.productid)";
				db.execute(query);
				
				//Update Balances 
				query = 'update Customer set balance = (select balance from balances b where b.clientid=Customer.id)';
				//query += '(select max(fecha) from Import where im.clientid=id))';
				db.execute(query); 
				query = 'update Customer set balance = 0 where balance is null';
				//query += '(select max(fecha) from Import where im.clientid=id))';
				db.execute(query); 
				//alert('updated balances');
				
				//change paytypes to uniform english
				query = 'update salePayment_2009 set paytype = "Cash" where paytype ="Efectivo"';
				db.execute(query);
				query = 'update salePayment_2009 set paytype = "Account" where paytype ="Cuenta"';
				db.execute(query);
				query = 'update salePayment_2009 set paytype = "Check" where paytype ="Cheque"';
				db.execute(query);
				 
				
			}
			
			 //loads/imports xml data
                        function LoadCity(zipcode){
                            if (window.XMLHttpRequest) {
                                xhttp2 = new XMLHttpRequest()
                            }
                            else {
                                xhttp2 = new ActiveXObject("Microsoft.XMLHTTP")
                            }
                            
                            xhttp2.open("GET", "cities.xml", false);
                            xhttp2.send("");
                            xmlDoc2 = xhttp2.responseXML;
                            
                            var y = xmlDoc2.getElementsByTagName("cities");
                            for (i = 0; i < y.length; i++) {
                                x = y[i].childNodes; //a specific city
                                if (x[1].childNodes[0].nodeValue == zipcode) //check if matches an id
                                {
                                    return x[3].childNodes[0].nodeValue;
                                }
                            }
                        }
        </script>
    </body>
</html>
